package com.gugee.tiktok.common.model.dal.blogger;

import com.gugee.tiktok.common.model.dto.SimpleAweme;
import com.gugee.tiktok.common.model.mysql.Aweme;
import com.gugee.tiktok.common.model.dto.BloggerAwemeExtendDto;
import com.gugee.tiktok.common.model.dto.BloggerRecordAwemeExtendDto;
import com.gugee.tiktok.data.common.msg.BloggerMessage;
import com.gugee.tiktok.data.common.msg.CommentMessage;
import org.springframework.data.jdbc.repository.query.Modifying;
import org.springframework.data.jdbc.repository.query.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Optional;

@Repository
public interface AwemeRepository extends CrudRepository<Aweme, Long>, AwemeExtRepository {
    Optional<Aweme> findByoAwemeId(long oAwemeId);
    List<Aweme> findByoAwemeIdIn(List<Long> oAwemeId);
    @Query("SELECT " +
            "COUNT(*) AS aweme_count, " +
            "IFNULL(ROUND(AVG(o_play_count)),0) AS avg_views, " +
            "IFNULL(ROUND(AVG(o_share_count)),0) AS avg_share, " +
            "IFNULL(ROUND(AVG(o_digg_count)),0) AS avg_likes, " +
            "IFNULL(ROUND(AVG(o_comment_count)),0) AS avg_reviews, " +
            "IFNULL(MAX(anchor),'NONE') as anchor " +
            "FROM aweme " +
            "WHERE o_uid=:uid and o_create_time>:min_time")
    Optional<BloggerAwemeExtendDto> getBloggerAwemeExtend(long uid, long min_time);

    @Query("SELECT " +
            "COUNT(*) AS aweme_count, " +
            "IFNULL(ROUND(SUM(o_play_count)),0) AS views_count, " +
            "IFNULL(ROUND(SUM(o_comment_count)),0) AS reviews_count, " +
            "IFNULL(ROUND(SUM(o_digg_count)),0) AS likes_count, " +
            "IFNULL(ROUND(SUM(o_share_count)),0) AS share_count " +
            "FROM aweme " +
            "WHERE o_uid=:uid and o_create_time>:min_time")
    Optional<BloggerRecordAwemeExtendDto> getBloggerRecordAwemeExtend(long uid, long min_time);

    @Query("SELECT " +
            "COUNT(*) AS aweme_count, " +
            "IFNULL(ROUND(SUM(o_play_count)),0) AS views_count, " +
            "IFNULL(ROUND(SUM(o_comment_count)),0) AS reviews_count, " +
            "IFNULL(ROUND(SUM(o_digg_count)),0) AS likes_count, " +
            "IFNULL(ROUND(SUM(o_share_count)),0) AS share_count " +
            "FROM aweme " +
            "WHERE o_uid=:uid and o_create_time BETWEEN :startDate AND :")
    Optional<BloggerRecordAwemeExtendDto> getBloggerRecordAwemeExtend(long uid, long startTime,long endTime);

    @Query("SELECT * FROM aweme WHERE o_uid=:uid And o_create_time >:minTime")
    List<Aweme> findAwemesByoUidAndoCreateTimeGreaterThan(long uid, Long minTime);

    @Query("SELECT * FROM aweme where o_uid = :uid and UNIX_TIMESTAMP()-o_create_time <=(86400+3540)")
    List<Aweme> findByUidAndBetween24Hour(long uid);

    @Query("SELECT " +
            "COUNT(o_uid) AS aweme_count " +
            "FROM aweme " +
            "WHERE o_uid=:uid and o_create_time>:min_time")
    int countAwemesByOUidAndCreateTime(long uid, long min_time);
    @Modifying
    @Query("UPDATE aweme c SET last_comment_cl_time=:commentClTime WHERE c.o_aweme_id = :oAwemeId  ")
    int updateAwemeCommentClTimeByoAwemeId(@Param("commentClTime") long commentClTime,@Param("oAwemeId") long oAwemeId);

    @Modifying
    @Query("UPDATE aweme c SET c.update_time = :updateTime , c.update_by=:updateBy ,syn_comments_with_reply=:synCommentsWithReply, syn_comments=:synComments ,last_comment_cl_time=:commentClTime WHERE c.o_aweme_id = :oAwemeId  ")
    int updateAwemeSynCommentsByoAwemeId(@Param("updateTime") long updateTime, @Param("updateBy") String updateBy,@Param("synComments") int synComments,@Param("synCommentsWithReply") int synCommentsWithReply,@Param("commentClTime") long commentClTime,@Param("oAwemeId") long oAwemeId);
    @Modifying
    @Query("UPDATE aweme c SET c.update_time = :updateTime , c.update_by=:updateBy , analysis_comments=:analysisComments WHERE c.o_aweme_id = :oAwemeId")
    int updateAwemeAnalysisCommentsByoAwemeId(@Param("updateTime") long updateTime, @Param("updateBy") String updateBy,@Param("analysisComments") int analysisComments,@Param("oAwemeId") long oAwemeId);



    @Query("SELECT id,o_uid uid,o_aweme_id,syn_comments,analysis_comments,o_comment_count,o_create_time FROM aweme" +
            " where o_uid = :uid And datecode >:minTime  And syn_comments <:maxComments AND last_comment_cl_time<:commentClTime " +
            " AND o_comment_count>(syn_comments + :stepComments) AND is_able = 1 AND if(o_comment_count >= syn_comments_with_reply, o_comment_count - syn_comments_with_reply, - (syn_comments_with_reply - o_comment_count))>0")
    List<CommentMessage> findCommentMessagesByUidAndRecentTimes(long uid,int minTime,int maxComments,int stepComments,long commentClTime);

    @Query(" SELECT id, uid,o_aweme_id,syn_comments,analysis_comments,o_comment_count,o_create_time FROM (" +
            "SELECT id,o_uid uid,o_aweme_id,syn_comments,analysis_comments,o_comment_count,o_create_time,syn_comments_with_reply,last_comment_cl_time FROM aweme " +
            " where datecode >:minTime AND aweme_type = :type   AND is_able = 1 " +
            " order by score desc LIMIT  :start,:records ) b" +
            " WHERE if(o_comment_count >= syn_comments_with_reply, o_comment_count - syn_comments_with_reply, - (syn_comments_with_reply - o_comment_count))>0" +
            " AND last_comment_cl_time<:commentClTime" +
            " And syn_comments <:maxComments And o_comment_count>(syn_comments + :stepComments)"
            )
    List<CommentMessage> getCommentsRankingOrderByScore(int minTime, String type, int maxComments, int stepComments, long start, long records,long commentClTime);

    @Query("SELECT id,o_aweme_id,o_create_time FROM aweme" +
            " where o_uid = :uid And o_create_time >:minTime  And analysis_comments < syn_comments")
    List<SimpleAweme> findKeywordMessagesByUidAndRecentTimes(long uid, Long minTime);
    @Query("SELECT uid,region,blogger_type,ranking FROM (SELECT o_uid as uid,o_region as region,blogger_type,'true' as ranking FROM aweme " +
            " where datecode >:miniTime AND aweme_type = :type "+
            " AND o_region != 'IN' " +
            " ORDER BY score DESC " +
            " LIMIT 0,:top ) a ,blogger_main b where a.uid=b.o_uid AND  b.tiktoker_status=0 " +
            " AND (UNIX_TIMESTAMP(now())-UNIX_TIMESTAMP(b.last_pub_time))/60/60>:intervalHours group by a.uid,a.region,a.blogger_type,a.ranking")
    List<BloggerMessage> findBloggerByTypeSortDescFromAweme(int miniTime, String type, long top,int intervalHours);

    /**
     * 根据 awemeId 更新是否可用字段
     * @param isAble
     * @param awemeId
     * @return
     */
    @Modifying
    @Query("UPDATE aweme t SET t.is_able = :isAble WHERE t.o_aweme_id = :awemeId ")
    int updateAwemeIsAbleByAwemeId(@Param("isAble") int isAble, @Param("awemeId") long awemeId);


    /**
     * 定时轮询 获取需要更新的粉丝画像视频数据（直接通过博主）
     * @param followerCount
     * @param start
     * @param count
     * @param minTime
     * @param recentTime
     * @return
     */
    @Query("SELECT t2.id, t2.o_uid, t2.o_aweme_id " +
            " FROM ( SELECT o_uid FROM blogger_main WHERE o_follower_count >= :followerCount AND o_region != 'IN' ORDER BY o_follower_count DESC LIMIT :start, :count ) t1 " +
            " INNER JOIN aweme t2 ON t1.o_uid = t2.o_uid " +
            " LEFT JOIN blogger_fans_portrait t3 ON t2.o_aweme_id = t3.aweme_id " +
            " WHERE t2.datecode > :minTime AND syn_comments > 0 AND ( t3.id IS NULL OR t3.update_time < :recentTime ) ")
    List<Aweme> getFansPortraitAwemeByFollowerAndRecentTimes(int followerCount, int start, int count, int minTime , long recentTime);

    /**
     * 定时 获取需要更新的粉丝画像视频数据
     * @param uids
     * @param minTime
     * @param recentTime
     * @param disabilityRate 残缺率
     * @return
     */
    @Query("SELECT t.id, t.o_uid, t.o_aweme_id  " +
            " from (SELECT id, o_uid, o_aweme_id from aweme WHERE o_uid in (:uids)      and datecode > :minTime AND syn_comments > 0 )t " +
            " LEFT JOIN blogger_fans_portrait t1 ON t.o_aweme_id = t1.aweme_id " +
            " WHERE t1.id IS NULL OR t1.update_time < :recentTime OR t1.need_analyze_blogger_count * 100 / t1.comments_sample_count > :disabilityRate ")
    List<Aweme> getFansPortraitAwemeByRecentTimes(List<Long> uids, int minTime , long recentTime,int disabilityRate);
}
